新機能 AWSCLIから Amazon Athena のクエリを実行する

新機能 AWSCLIから Amazon Athena のクエリを実行する

#AWS CLI
#Amazon Athena
#AWS
石川覚

石川覚

facebook logohatena logotwitter logo
Clock Icon2017.05.21

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

昨日、以前から要望の多かった AWSCLI から Amazon Athena のクエリ実行する機能がリリースされましたので、早速試してみました。

Amazon Athena adds API/CLI, AWS SDK support, and audit logging with AWS CloudTrail

今回のリリースでは、AWSCLIの他に Java, .NET, Node.js, PHP, Python, Ruby, Go, 及び C++ で利用可能な AWS SDK を使用して、REST API 経由で Amazon Athena からもクエリを実行できるようになりました。詳細については、APIリファレンスおよびCLIガイドを参照してください。

サポートされた機能

ペタバイトクラスのクエリ実行も想定した Amazon Athena なので、クエリ実行は、「クエリの非同期実行〜クエリ実行状態の確認〜クエリ実行結果の取得」という流れになります。

AWS CLI Command Reference

AWSCLIの実行例などありませんでしたが、AWS SDK for Java の Examples and Code Samples を例に読み解き、サブコマンドを分類したり、ユースケースを私なりに解説したいと思います。

サブコマンド

準備 〜 AWSCLIを最新に更新

最新のASCLIに更新します。検証はMacBook、AWSCLIのバージョンは1.11.89で確認しました。

$ aws --version
aws-cli/1.11.73 Python/2.7.10 Darwin/16.6.0 botocore/1.5.36

$ sudo pip install --upgrade awscli --ignore-installed six
Password:
：
（中略）
：
Successfully installed PyYAML-3.12 awscli-1.11.89 botocore-1.5.52 colorama-0.3.7 docutils-0.13.1 futures-3.1.1 jmespath-0.9.2 pyasn1-0.2.3 python-dateutil-2.6.0 rsa-3.4.2 s3transfer-0.1.10 six-1.10.0

$ aws --version
aws-cli/1.11.89 Python/2.7.10 Darwin/16.6.0 botocore/1.5.52

検証用テーブルの作成

検証データは、AWSが提供しているテストデータのlineorderテーブル(75004738レコード)を利用します。

s3://awssampledb/ssbgz/lineorder0000_part_00.gz

-- DROP TABLE cmathenadb.lineorder;
CREATE EXTERNAL TABLE IF NOT EXISTS cmathenadb.lineorder (
lo_orderkey int,
lo_linenumber int,
lo_custkey int,
lo_partkey int,
lo_suppkey int,
lo_orderdate int,
lo_orderpriority string,
lo_shippriority string,
lo_quantity int,
lo_extendedprice int,
lo_ordertotalprice int,
lo_discount int,
lo_revenue int,
lo_supplycost int,
lo_tax int,
lo_commitdate int,
lo_shipmode string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '|',
'field.delim' = '|'
) LOCATION 's3://cm-datalake/lineorder/';

「単一のクエリー」を実行する

「単一のクエリー」を実行は、「クエリ実行の開始〜クエリ実行状況の確認〜クエリ実行結果の取得」という流れになります。実行したクエリを中断することも可能です。

クエリ実行の開始：start-query-execution

クエリの実行は、start-query-executionサブコマンドを使います。 --query-stringパラメータに実行したいSQL、--result-configurationパラメータに実行結果を保存するS3のパスを指定します。（JDBCドライバで指定するS3_Staging_Dirに相当します）

実行すると クエリの実行を待たずに終了し、代わりに QueryExecutionId が直ちに返ってきます。以降はこのQueryExecutionIdをキーに操作します。

$ aws athena start-query-execution \
> --query-string "SELECT * FROM cmathenadb.lineorder limit 10;" \
> --result-configuration OutputLocation=s3://cm-datalake/awscli_staging_dir
{
"QueryExecutionId": "e397cfa4-aa12-41c9-aebf-aa629bba7453"
}

引数に渡したクエリーに誤りがあるなどの場合は非同期ではなく、直ちにエラーメッセージが返ってきます。クエリーにエラーがあった場合、エラーメッセージが標準出力に出力されます。

$ aws athena start-query-execution \
> --query-string "SELECT ** FROM cmathenadb.lineorder limit 10;" \
> --result-configuration OutputLocation=s3://cm-datalake/awscli_staging_dir

An error occurred (InvalidRequestException) when calling the StartQueryExecution operation: line 1:9: extraneous input '*' expecting {, ',', 'FROM', 'WHERE', 'GROUP', 'ORDER', 'HAVING', 'LIMIT', 'APPROXIMATE', 'UNION', 'EXCEPT', 'INTERSECT'}

クエリ実行状況の確認：get-query-execution

バックグラウンドでクエリが実行していますので、クエリが実行状況や結果の取得が可能であるか等、クエリの実行状況を確認します。

$ aws athena get-query-execution --query-execution-id e397cfa4-aa12-41c9-aebf-aa629bba7453
{
"QueryExecution": {
"Status": {
"SubmissionDateTime": 1495305498.251,
"State": "SUCCEEDED",
"CompletionDateTime": 1495305499.987
},
"Query": "SELECT * FROM cmathenadb.lineorder limit 10",
"Statistics": {
"DataScannedInBytes": 220743,
"EngineExecutionTimeInMillis": 1473
},
"ResultConfiguration": {
"OutputLocation": "s3://cm-datalake/awscli_staging_dir/e397cfa4-aa12-41c9-aebf-aa629bba7453.csv"
},
"QueryExecutionId": "e397cfa4-aa12-41c9-aebf-aa629bba7453"
}
}

クエリ実行結果の取得：get-query-results

上記の結果、QueryExecution.Status.Stateが "SUCCEEDED"（クエリは実行済）なので、結果を取得します。 ResultSet.Rows.Dataはレコードが繰り返し出力します。ResultSetMetadata.ColumnInfoにはカラム情報の詳細が含まれています。

$ aws athena get-query-results --query-execution-id e397cfa4-aa12-41c9-aebf-aa629bba7453
{
"ResultSet": {
"Rows": [
{
"Data": [
{
"VarCharValue": "lo_orderkey"
},
{
"VarCharValue": "lo_linenumber"
},
{
"VarCharValue": "lo_custkey"
},
{
"VarCharValue": "lo_partkey"
},
{
"VarCharValue": "lo_suppkey"
},
{
"VarCharValue": "lo_orderdate"
},
{
"VarCharValue": "lo_orderpriority"
},
{
"VarCharValue": "lo_shippriority"
},
{
"VarCharValue": "lo_quantity"
},
{
"VarCharValue": "lo_extendedprice"
},
{
"VarCharValue": "lo_ordertotalprice"
},
{
"VarCharValue": "lo_discount"
},
{
"VarCharValue": "lo_revenue"
},
{
"VarCharValue": "lo_supplycost"
},
{
"VarCharValue": "lo_tax"
},
{
"VarCharValue": "lo_commitdate"
},
{
"VarCharValue": "lo_shipmode"
}
]
},
{
"Data": [
{
"VarCharValue": "526035463"
},
{
"VarCharValue": "1"
},
{
"VarCharValue": "1327621"
},
{
"VarCharValue": "833550"
},
{
"VarCharValue": "41158"
},
{
"VarCharValue": "19971130"
},
{
"VarCharValue": "5-LOW"
},
{
"VarCharValue": "0"
},
{
"VarCharValue": "9"
},
{
"VarCharValue": "1335159"
},
{
"VarCharValue": "21320828"
},
{
"VarCharValue": "10"
},
{
"VarCharValue": "1201643"
},
{
"VarCharValue": "89010"
},
{
"VarCharValue": "0"
},
{
"VarCharValue": "19980111"
},
{
"VarCharValue": "MAIL"
}
]
},
：
（中略）
：
],
"ResultSetMetadata": {
"ColumnInfo": [
{
"Scale": 0,
"Name": "lo_orderkey",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_orderkey",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_linenumber",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_linenumber",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_custkey",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_custkey",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_partkey",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_partkey",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_suppkey",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_suppkey",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_orderdate",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_orderdate",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_orderpriority",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 1073741824,
"Label": "lo_orderpriority",
"CaseSensitive": true,
"SchemaName": "",
"Type": "varchar",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_shippriority",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 1073741824,
"Label": "lo_shippriority",
"CaseSensitive": true,
"SchemaName": "",
"Type": "varchar",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_quantity",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_quantity",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_extendedprice",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_extendedprice",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_ordertotalprice",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_ordertotalprice",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_discount",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_discount",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_revenue",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_revenue",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_supplycost",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_supplycost",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_tax",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_tax",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_commitdate",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_commitdate",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_shipmode",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 1073741824,
"Label": "lo_shipmode",
"CaseSensitive": true,
"SchemaName": "",
"Type": "varchar",
"CatalogName": "hive"
}
]
}
}
}

実行クエリの中断：stop-query-execution

なお、バックグラウンドで実行しているこのクエリの実行を中断したいときは、stop-query-executionを実行します。

$ aws athena stop-query-execution --query-execution-id e397cfa4-aa12-41c9-aebf-aa629bba7453

クエリの結果をjsonではなく、CSV形式で取得したいときは？

get-query-resultsサブコマンドは、プログラムで構造化したjsonファイルから必要な値をピンポイントで取得するのに便利ですが、普通にCSVファイルとして取得したいことがあるでしょう。そんなときは、Athenaは実行結果をS3にCSV出力しているので、そのCSVファイルで取得したり、標準出力に出力するという方法が手っ取り早いのでおすすめです。

$ aws s3 cp s3://cm-datalake/awscli_staging_dir/e397cfa4-aa12-41c9-aebf-aa629bba7453.csv -
"lo_orderkey","lo_linenumber","lo_custkey","lo_partkey","lo_suppkey","lo_orderdate","lo_orderpriority","lo_shippriority","lo_quantity","lo_extendedprice","lo_ordertotalprice","lo_discount","lo_revenue","lo_supplycost","lo_tax","lo_commitdate","lo_shipmode"
"526035463","1","1327621","833550","41158","19971130","5-LOW","0","9","1335159","21320828","10","1201643","89010","0","19980111","MAIL"
"457881700","1","849551","682835","698046","19950803","2-HIGH","0","33","5998740","19697557","7","5578828","109068","0","19950912","AIR"
"457881762","5","975452","341456","874640","19970524","1-URGENT","0","38","5690272","19069420","9","5178147","89846","8","19970815","MAIL"
"457881829","3","2047915","442604","490246","19950424","5-LOW","0","29","4485082","17152942","9","4081424","92794","3","19950524","SHIP"
"457881894","3","2855755","108553","372555","19920816","2-HIGH","0","33","5153115","26092287","10","4637803","93693","0","19921016","RAIL"
"457881956","4","2286178","511197","88971","19930217","5-LOW","0","37","4470229","19623933","4","4291419","72490","6","19930409","TRUCK"
"457882019","3","2473054","958777","403362","19951106","5-LOW","0","10","1835730","19051236","5","1743943","110143","3","19951231","MAIL"
"457882081","1","121738","240519","706437","19920121","4-NOT SPECI","0","0","0","0","8","0","87570","3","19920223","AIR"
"457882117","2","2762990","547842","49430","19930729","3-MEDIUM","0","38","7181316","7208005","6","6750437","113389","5","19930922","REG AIR"
"457882182","1","2670493","755731","11430","19980705","5-LOW","0","9","1608030","29117346","6","1511548","107202","3","19980901","RAIL"

まとめ

シンプルですが必要な機能がよくまとまったアップデートです。これまでバッチ処理はJDBCドライバによるプログラミングに限定されていましたが、AWSCLIのみで簡単に実装できるようになりました。AmazonAthena の東京リージョンのサービス開始が待ち遠しい限りです。

Share this article

facebook logohatena logotwitter logo

関連記事

AWS ChatbotをSlackと連携させて、SlackからAWS CLIを使ってみた

AWS ChatbotをSlackと連携させて、SlackからAWS CLIを使ってみた

User avatar
sora
2024.11.14
Cognito ユーザープールとIDプールを使用した認証ユーザーによるS3ファイルアップロードを試してみた

Cognito ユーザープールとIDプールを使用した認証ユーザーによるS3ファイルアップロードを試してみた

User avatar
平井裕二
2024.11.13
Amazon Rekognition向けのAWS CLIコマンドでカスタムモデレーションのリソースを操作するときのオプション

Amazon Rekognition向けのAWS CLIコマンドでカスタムモデレーションのリソースを操作するときのオプション

User avatar
nayu.t.s
2024.11.05
AWS Marketplace の製品コードから AMI に関する情報を取得する方法を教えてください

AWS Marketplace の製品コードから AMI に関する情報を取得する方法を教えてください

User avatar
片方 裕人
2024.11.03
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.